﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Xml.Serialization;
using System.IO;
using System.Runtime.InteropServices;


namespace TestBrownser
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        #region RangeHelper
        private static string GetCellValue(Excel.Range allRange, int rowIndex, int columnIndex)
        {
            return (GetRange(allRange, rowIndex, columnIndex).Value ?? string.Empty).ToString();
        }

        private static Excel.Range GetRange(Excel.Range allRange, int rowIndex, int columnIndex)
        {
            return (Excel.Range)allRange[rowIndex, columnIndex];
        }
        #endregion

        #region Configs
        private static int GetColumnIndexByPropName(MapConfig config, Excel.Range allRange, string propName)
        {
            int periodEndColumnIndex = GetColumnIndexHasValue(config.GetProp(propName).ColumnCaption, allRange);
            if (periodEndColumnIndex < 0)
                periodEndColumnIndex = config.GetProp(propName).ColumnIndex;
            return periodEndColumnIndex;
        }

        private static int GetColumnIndexHasValue(string value, Excel.Range allRange)
        {
            int result = -1;
            int rowIndex = 1;
            for (int columnIndex = 1; columnIndex < allRange.Columns.Count; columnIndex++)
            {
                if (GetCellValue(allRange, rowIndex, columnIndex) == value)
                {
                    result = columnIndex;
                    break;
                }
            }
            return result;
        }
        #endregion

        #region Html
        private void FillTable()
        {
            string attrName = "value";
            string tableName = "GV1";
            HtmlDocument doc = webBrowser1.Document; //获取document对象

            if (doc == null)
            {
                MessageBox.Show("请先打开网页");
                return;
            }

            HtmlElement table = GetTable(tableName, doc);

            if (table == null)
            {
                MessageBox.Show((string.Format("没有找到页面中名为“{0}”的表", tableName)));
                return;
            }

            DealWithTable(attrName, table);
        }

        private static HtmlElement GetTable(string tableName, HtmlDocument doc)
        {

            HtmlElement table = doc.GetElementById(tableName);
            if (table != null)
            {
                return table;
            }

            foreach (HtmlWindow item in doc.Window.Frames)
            {
                table = GetTable(tableName, item.Document);
                if (table != null)
                {
                    return table;
                }
            }

            return table;
        }

        private void DealWithTable(string attrName, HtmlElement table)
        {
            HtmlElement tableBody = table.FirstChild;
            foreach (HtmlElement tr in tableBody.Children)
            {
                if (tr.TagName.ToUpper() == "TR")
                {
                    string id = tr.Children[2].InnerText;
                    Repoository item = getRepositoryById(id);
                    if (item != null)
                    {
                        try
                        {
                            tr.Children[7].Children[0].SetAttribute(attrName, item.PeriodEndAmount);
                            tr.Children[8].Children[0].SetAttribute(attrName, item.OnRoadAmount);
                        }
                        catch (Exception ex)
                        {
                            throw new ApplicationException("没有找到单元格");
                        }
                    }
                }
            }
            MessageBox.Show("填表完成");
        }

        #endregion

        #region Repositories
        List<Repoository> repositories = new List<Repoository>();

        private Repoository getRepositoryById(string id)
        {
            return repositories.Where(r => r.ID.Trim() == id).SingleOrDefault();
        }

        private void LoadRespositories()
        {
            MapConfig config = new MapConfig();
            config.Load();

            string shtName = "HY";
            repositories.Clear();
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Excel files (*.xls,*.xlsx)|*.xls*|All files (*.*)|*.*";
            if (DialogResult.OK == dlg.ShowDialog())
            {
                Excel.ApplicationClass xApp = null;
                int xx = 0;
                try
                {
                    xApp = new Excel.ApplicationClass();
                    string fileName = dlg.FileName;
                    //TODO
                    var xBook = xApp.Workbooks.Open(fileName);

                    List<string> sheetNames = new List<string>();
                    foreach (Excel.Worksheet sht in xBook.Sheets)
                    {
                        sheetNames.Add(sht.Name);
                    }

                    using (var frm = new FrmItemSelect<string>(sheetNames))
                    {
                        frm.Summray = "从下面列表中选择你要导入数据的表名";
                        if (frm.ShowDialog() == DialogResult.OK)
                        {
                            if (frm.SelectedItem != null)
                            {
                                shtName = frm.SelectedItem;
                            }
                        }
                    }


                    Excel.Worksheet xSheet = xBook.Worksheets[shtName] as Excel.Worksheet;



                    var allRange = xSheet.UsedRange;
                    if (allRange.Rows.Count > 1)
                    {
                        //title row 
                        int idColumnIndex = GetColumnIndexByPropName(config, allRange, "ID");
                        int onRoadColumnIndex = GetColumnIndexByPropName(config, allRange, "OnRoadAmount");
                        int periodEndColumnIndex = GetColumnIndexByPropName(config, allRange, "PeriodEndAmount");
                        if (idColumnIndex < 0 || onRoadColumnIndex < 0 || periodEndColumnIndex < 0)
                        {
                            MessageBox.Show("配置文件出错");
                        }
                        for (int i = 1; i <= allRange.Rows.Count; i++)
                        {
                            xx = i;
                            string id = (((Excel.Range)allRange[i, idColumnIndex]).Value ?? string.Empty).ToString();
                            string onRoadAmount = (((Excel.Range)allRange[i, onRoadColumnIndex]).Value ?? string.Empty).ToString();
                            string periodEndAmount = (((Excel.Range)allRange[i, periodEndColumnIndex]).Value ?? string.Empty).ToString();

                            if (!string.IsNullOrEmpty(id)
                                && (!string.IsNullOrEmpty(onRoadAmount)
                                && !string.IsNullOrEmpty(periodEndAmount)))
                                repositories.Add(new Repoository { ID = id, OnRoadAmount = onRoadAmount, PeriodEndAmount = periodEndAmount });
                        }
                    }
                    else
                    {
                        MessageBox.Show(" 没有在文件{0}中的{1}表发现数据。");
                    }
                    xApp.Workbooks[1].Close();
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    xApp.Quit();
                    Quit(xApp);
                }
            }
        }

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        private void Quit(Excel._Application excelApp)
        {
            IntPtr t = new IntPtr(excelApp.Hwnd);   //得到这个句柄，具体作用是得到这块内存入口 
            int k = 0;
            GetWindowThreadProcessId(t, out k);   //得到本进程唯一标志k
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);   //得到对进程k的引用
            p.Kill();     //关闭进程k
        }

        #endregion

        #region Events
        private void btnGo_Click(object sender, EventArgs e)
        {
            webBrowser1.Navigate(txtUrl.Text);
            SaveUrl(txtUrl.Text);
        }

        private void txtUrl_DoubleClick(object sender, EventArgs e)
        {
            txtUrl.SelectAll();
        }

        private void btnLoadData_Click(object sender, EventArgs e)
        {
            LoadRespositories();
            if (repositories.Count == 0)
            {
                MessageBox.Show("没有从excel文件中提到数据");
                return;
            }
            FillTable();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.txtUrl.Text = "file:///C:/Users/acer/Desktop/INVInput[1].html";
            this.txtUrl.Text = "file:///C:/Documents and Settings/Administrator/桌面/INVInput.html";
            this.txtUrl.Text = ReadUrl();
            webBrowser1.ScriptErrorsSuppressed = true;
        }
        #endregion

        #region UrlSave
        private string ReadUrl()
        {
            string url = string.Empty;
            using (FileStream fs = new FileStream(@"lasturl", FileMode.OpenOrCreate))
            {
                byte[] bytes = new byte[fs.Length];
                fs.Read(bytes, 0, (int)fs.Length);
                url = Encoding.UTF8.GetString(bytes);
            }
            return url;
        }

        private void SaveUrl(string url)
        {
            using (FileStream fs = new FileStream(@"lasturl", FileMode.Create))
            {
                byte[] bytes = Encoding.UTF8.GetBytes(url);
                fs.Write(bytes, 0, bytes.Length);
            }
        }
        #endregion
    }

    class Repoository
    {
        public string ID { get; set; }
        public string PeriodEndAmount { get; set; }
        public string OnRoadAmount { get; set; }
        public string Memo { get; set; }
    }
}
